package com.mairuide.bill;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.stream.Collectors;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.mairuide._frame.base.BaseController;
import com.mairuide._frame.base.ModuleCode;
import com.mairuide._frame.base.PageSupport;
import com.mairuide._frame.utils.GsonUtil;
import com.mairuide._frame.utils.MD5;
import com.mairuide._frame.utils.ThreadPoolUtils;
import com.mairuide._frame.utils.sheethandler.POIFactory;
import com.mairuide.bill.importbill.TemplateMatchService;
import com.mairuide.bill.titlepool.DealFlowTitle;
import com.mairuide.bill.titlepool.DealFlowTitlePoolService;
import com.mairuide.bill.titlepool.DealFlowTitleService;
import com.mairuide.bill.utils.DateUtils;
import com.mairuide.bill.utils.DownloadUtils;
import com.mairuide.bill.utils.ExcelUtil;
import com.mairuide.bill.utils.ExcelUtils;
import com.mairuide.bill.utils.ZipUtils;
import com.mairuide.casemanage.dao.BillTempImportParams;
import com.mairuide.casemanage.service.CaseManageService;
import com.mairuide.datalog.ActionType;
import com.mairuide.datalog.DataOperationImportLog;
import com.mairuide.datalog.DataOperationImportLogService;
import com.mairuide.datalog.DataOperationLogService;
import com.mairuide.datalog.ModuleFlag;
import com.mairuide.datalog.SystemFlag;
import com.mairuide.entity.PageResult;
import com.mairuide.entity.Result;
import com.mairuide.entity.StatusCode;
import com.mairuide.exception_analysis.pojo.ExceptionAnalysisDto;
import com.mairuide.phone.PhoneRecord;

@Controller
@RequestMapping("/bill")
public class DealFlowController extends BaseController {

	@Resource
	private TemplateMatchService templateMatchService;

	@Resource
	private DataOperationLogService dataOperationLogService;

	@Resource
	private DealFlowService dealFlowService;

	@Resource
	private DealFlowTitlePoolService dealFlowTitlePoolService;

	@Resource
	private DealFlowTitleService dealFlowTitleService;

	@Autowired
	private DataOperationImportLogService dataOperationImportLogService;

	@Autowired
	private CaseManageService caseManageService;
	
	@ModuleCode(pValue = "billimport_export", value = "billimport")
	@RequestMapping(value = "/import", method = RequestMethod.GET)
	public Object importFile(ModelMap model, PageSupport pagesupport) {
		model.addAttribute("menu", "import");
		return "/bill/importBill";
	}

	@ModuleCode(pValue = "billimport_export", value = "billexport")
	@RequestMapping(value = "/export", method = RequestMethod.GET)
	public Object exportFile(DealFlow dealFlow, ModelMap model, PageSupport pagesupport) {
		return "/bill/exportBill";
	}

	@ResponseBody
	@RequestMapping(value = "/search/{page}/{size}", method = RequestMethod.POST)
	public Result exportFile2(@RequestBody(required = false) DealFlow dealFlow, @PathVariable int page,
			@PathVariable int size) {
		String caseId = dealFlow.getCasemanageId();
		Map whereMap = new HashMap();
		whereMap.put("accountBody", dealFlow.getAccountBody());
		whereMap.put("accountBodyNumber", dealFlow.getAccountBodyNumber());
		whereMap.put("accountBodyCard", dealFlow.getAccountBodyCard());
		whereMap.put("casemanageId", caseId);
		PageResult pageResult = dealFlowService.getPageDetails(whereMap, page, size);
		return new Result(true, StatusCode.OK, "查询成功", pageResult);
	}

	/**
	 * 文件导入
	 *
	 * @param files
	 * @throws IOException
	 */
	@RequestMapping("/uploadFile")
	@ResponseBody
	public void uploadFile(@RequestParam("fileUpload") MultipartFile[] files, HttpServletRequest request,
			HttpServletResponse response) {
		List<Map<String, Object>> mapList = new ArrayList<>();
		String uid = request.getSession().getAttribute("caseId") == null ? ""
				: request.getSession().getAttribute("caseId").toString();
		String uuid = null;
		String filename = null;
		List<DataOperationImportLog> dils = new ArrayList<DataOperationImportLog>();
		try {
			for (int i = 0; i < files.length; i++) {
				Map<String, Object> map = new HashMap<>();
				try {
					MultipartFile file = files[i];
					filename = file.getOriginalFilename().split("\\.")[0];
					uuid = UUID.randomUUID().toString().replace("-", "");
					// 判断文件类型
					if (file == null || file.getSize() <= 0) {
						map.put("error", file.getOriginalFilename());
					}
					String result = ExcelUtils.getFileSort(file);
					if ("excel".equals(result)) {
						map = templateMatchService.importExcelTemplete(file, uid, super.getLoginUser(), uuid);
					} else if ("csv".equals(result)) {
						map = dealFlowTitlePoolService.leadFileCSV(file, uid, super.getLoginUser().getId(), uuid);
					} else if ("txt".equals(result)) {
						map = templateMatchService.importTxtTemplete(file, uid, super.getLoginUser().getId(), uuid);
					} else if ("pdf".equals(result)) {
						map = dealFlowTitleService.pdfAnalysis(file, uid, super.getLoginUser(), uuid);
					}
					/*
					 * if (((List<DealFlow>) map.get("list")).size()>0){
					 * list.addAll((List<DealFlow>)map.get("list")); uuid += ","+((List<DealFlow>)
					 * map.get("list")).get(0).getUuid(); number +=
					 * Integer.parseInt((String)map.get("successNum")); map.put("list",list);
					 * map.put("successNum",String.valueOf(number)); }
					 */
					map.put("fileName", filename);
					mapList.add(map);
					DataOperationImportLog dil = new DataOperationImportLog();
					dil.setFileName(filename);
					dil.setDealFlowUuid(uuid);
					dil.setSuccessNumber(
							map.get("successNum") == null ? 0 : Integer.parseInt(map.get("successNum").toString()));
					dil.setTableFlat(filename.equals("开户基本信息表") ? 0 : 1);
					dil.setFileSize(String.valueOf(file.getSize()));
					dil.setUserId(super.getLoginUser().getId());
					dil.setCreateTime(new Timestamp(System.currentTimeMillis()));
					dils.add(dil);
				} catch (Exception e) {
					map.put("successNum", 0);
					mapList.add(map);
					e.printStackTrace();
				}
			}
			String caseManageId = request.getSession().getAttribute("caseId") == null ? ""
					: request.getSession().getAttribute("caseId").toString();
			// 插入导入日志
			dataOperationLogService.save(files.length, dils, super.getLoginUser(), SystemFlag.ZD, ModuleFlag.BILLINPUT,
					ActionType.INPUT, caseManageId);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				for (int i = 0; i < files.length; i++) {
					MultipartFile file = files[i];
					// 文件上传
					super.persistFile(file);
				}
				response.getWriter().print(GsonUtil.GsonString(SUCCESS(mapList)));
				/*
				 * if (map!=null){ //如果是资金查控平台 目的是把开户信息的excel的值更新到流水表中 if
				 * (fileNames.contains("开户基本信息表")||fileNames.contains("账户交易明细表")) { List<String>
				 * bodyCardList = dealFlowService.findByAccountBodyCardList(uid);
				 * dealFlowService.batchUpdate(bodyCardList,uid); }
				 * response.getWriter().print(GsonUtil.GsonString(SUCCESS(mapList))); }else {
				 * response.getWriter().print(GsonUtil.GsonString(ERROR("解析文件失败"))); }
				 */
			} catch (IOException e) {
				e.printStackTrace();
			}
			/* 缓存首页预加载内容start */
			dealFlowService.clearCache1(uid);
			dealFlowService.clearCache2(uid);
			dealFlowService.clearCache3(uid);
			dealFlowService.clearCache4(uid);
			dealFlowService.clearCache5(uid);
			/* 缓存首页预加载内容end */
			ThreadPoolUtils.getInstance().getThreadPool().execute(new Runnable() {
				@Override
				public void run() {
					try {
						dealFlowService.batchUpdate(uid);
					} catch (Exception e2) {
						e2.printStackTrace();
					} finally {
						dealFlowService.clearCache1(uid);
						dealFlowService.clearCache2(uid);
						dealFlowService.clearCache3(uid);
						dealFlowService.clearCache4(uid);
						dealFlowService.clearCache5(uid);
						// 更新人员信息
						dealFlowService.updatePersonInformation(uid);
					}
				}
			});

		}
	}

	@RequestMapping("/billExport")
	public void billExport2(HttpServletResponse response, DealFlow dealFlow) throws Exception {
		String path = "D:\\data\\zip";
		File dir = new File(path);
		if (!dir.exists()) {
			dir.mkdir();
		}
		String pathUUID = path + "\\" + UUID.randomUUID().toString();
		int pageSise = 50000;
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("yyyy-MM-dd");
		String caseId = dealFlow.getCasemanageId();

		Map whereMap = new HashMap();
		whereMap.put("accountBody", dealFlow.getAccountBody());
		whereMap.put("accountBodyNumber", dealFlow.getAccountBodyNumber());
		whereMap.put("accountBodyCard", dealFlow.getAccountBodyCard());
		whereMap.put("casemanageId", caseId);
		PageResult pageResult = dealFlowService.getPageDetails(whereMap, 1, pageSise);
		Long total = pageResult.getTotal();

		long totalPages = total % pageSise == 0 ? total / pageSise : total / pageSise + 1;

		String[] titles = "交易主体,交易主体账号,交易主体卡号,收付标志,交易日期,交易对手,交易对手账号,交易对手卡号,交易金额,交易后余额,摘要,交易主体归属行,交易对手归属行,交易地点,交易方式,交易流水号,MAC地址,IP地址,币种,备注,交易机构号,柜员号,对方机构号"
				.split(",");
		for (int q = 0; q < totalPages; q++) {
			SXSSFWorkbook wb = new SXSSFWorkbook(1000); // 阈值，内存中的对象数量最大数量
			SXSSFSheet sheet = wb.createSheet();
			List<DealFlow> list = new ArrayList<DealFlow>();
			Row row = sheet.createRow(0);
			int titleIndex = 0;
			for (String title : titles) {
				Cell cell = row.createCell(titleIndex++);
				cell.setCellValue(title);
			}
			// 添加过滤-start
			String addStr = "A0:" + ExcelUtil.letter[titles.length - 1] + "0";
			CellRangeAddress addr = CellRangeAddress.valueOf(addStr);
			sheet.setAutoFilter(addr);
			// 添加过滤-end

			int rowIndex = 1;
			Cell cell = null;
			if (q == 0) {
				list = pageResult.getRows();
			} else {
				pageResult = dealFlowService.getPageDetails(whereMap, q + 1, pageSise);
				list = pageResult.getRows();
			}
			for (DealFlow u : list) {
				row = sheet.createRow(rowIndex++);
				// 交易主体,
				cell = row.createCell(0);
				cell.setCellValue(u.getAccountBody());
				// 交易主体账号,
				cell = row.createCell(1);
				cell.setCellValue(u.getAccountBodyNumber());
				// 交易主体卡号,
				cell = row.createCell(2);
				cell.setCellValue(u.getAccountBodyCard());
				// 收付标志,
				cell = row.createCell(3);
				cell.setCellValue(u.getSign() == 0 ? "进" : "出");
				// 交易日期,
				cell = row.createCell(4);
				cell.setCellValue(u.getTradeDate() == null ? "" : simpleDateFormat.format(u.getTradeDate()));
				// 交易对手,
				cell = row.createCell(5);
				cell.setCellValue(u.getAccountRival());
				// 交易对手账号,
				cell = row.createCell(6);
				cell.setCellValue(u.getAccountRivalNumber());
				// 交易对手卡号,
				cell = row.createCell(7);
				cell.setCellValue(u.getAccountRivalCard());
				// 交易金额,
				cell = row.createCell(8);
				cell.setCellValue(u.getAccountMoney() == null ? "" : String.valueOf(u.getAccountMoney()));
				// 交易后余额,
				cell = row.createCell(9);
				cell.setCellValue(u.getSurplusMoney() == null ? "" : String.valueOf(u.getSurplusMoney()));
				// 摘要,
				cell = row.createCell(10);
				cell.setCellValue(u.getContentAbstract());
				// 交易主体归属行,
				cell = row.createCell(11);
				cell.setCellValue(u.getAccountBodyBank());
				// 交易对手归属行,
				cell = row.createCell(12);
				cell.setCellValue(u.getAccountRivalBank());
				// 交易地点,
				cell = row.createCell(13);
				cell.setCellValue(u.getAccountAddress());
				// 交易方式,
				cell = row.createCell(14);
				cell.setCellValue(u.getAccountMode());
				// 交易流水,
				cell = row.createCell(15);
				cell.setCellValue(u.getSerialNumber());
				// mac,
				cell = row.createCell(16);
				cell.setCellValue(u.getMacAddress());
				// ip,
				cell = row.createCell(17);
				cell.setCellValue(u.getIpAddress());
				// 币种,
				cell = row.createCell(18);
				cell.setCellValue(u.getCurrency());
				// 备注,
				cell = row.createCell(19);
				cell.setCellValue(u.getRemarks());
				// 交易机构号,
				cell = row.createCell(20);
				cell.setCellValue(u.getTradingOrganizationNumber());
				// 柜员号,
				cell = row.createCell(21);
				cell.setCellValue(u.getTellerNumber());
				// 对方机构号,
				cell = row.createCell(22);
				cell.setCellValue(u.getInstitutionNumber());

			}
			DownloadUtils.writeExcel2(wb, pathUUID, "账户交易明细表" + q);
			list = null;
		}
		File zip = new File(path, "银行账单" + simpleDateFormat2.format(new Date()) + ".zip");
		File uuidDir = new File(pathUUID);
		if (uuidDir != null && uuidDir.isDirectory()) {
			File[] files = uuidDir.listFiles();
			List<File> xlsx = Arrays.stream(files).filter(m -> m.getName().contains("xlsx"))
					.collect(Collectors.toList());
			ZipUtils.ZipFiles(xlsx, zip);
		}
		DownloadUtils.downFileByStream2(response, zip, "银行账单" + simpleDateFormat2.format(new Date()), pathUUID);
		dataOperationLogService.save(null, super.getLoginUser(), new DealFlow(), new DealFlow(), SystemFlag.ZD,
				ModuleFlag.BILLEXPORT, ActionType.EXPORT);
	}

	/**
	 * @Author 刘中华
	 * @Date 2019/3/27 14:14
	 * @Param 原始账单导出，就是说用户导进去的是哪几个账单，就给他导出那几个账单
	 * @return
	 * @Description
	 **/
	@RequestMapping("/billExport3")
	public void billExport3(HttpServletResponse response, DealFlow dealFlow) throws Exception {
		String path = "D:\\data\\zip";
		File dir = new File(path);
		if (!dir.exists()) {
			dir.mkdir();
		}
		String pathUUID = path + "\\" + UUID.randomUUID().toString();
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("yyyy-MM-dd");
		String[] titles = "交易主体,交易主体账号,交易主体卡号,收付标志,交易日期,交易对手,交易对手账号,交易对手卡号,交易金额,交易后余额,摘要,交易主体归属行,交易对手归属行,交易地点,交易方式,交易流水号,MAC地址,IP地址,币种,备注,交易机构号,柜员号,对方机构号"
				.split(",");
		String caseManagerId = dealFlow.getCasemanageId();
		// 第一步，得到导入了几个账单
		List<DataOperationImportLog> byDealFlowUuid = dataOperationImportLogService.findByDealFlowUuid(caseManagerId);
		for (int i = 0; i < byDealFlowUuid.size(); i++) {
			List<DealFlow> list = dealFlowService.findByUuid(caseManagerId, byDealFlowUuid.get(i).getDealFlowUuid());
			SXSSFWorkbook wb = new SXSSFWorkbook(1000); // 阈值，内存中的对象数量最大数量
			SXSSFSheet sheet = wb.createSheet();
			Row row = sheet.createRow(0);
			int titleIndex = 0;
			for (String title : titles) {
				Cell cell = row.createCell(titleIndex++);
				cell.setCellValue(title);
			}
			// 添加过滤-start
			String addStr = "A0:" + ExcelUtil.letter[titles.length - 1] + "0";
			CellRangeAddress addr = CellRangeAddress.valueOf(addStr);
			sheet.setAutoFilter(addr);
			// 添加过滤-end

			int rowIndex = 1;
			Cell cell = null;
			for (DealFlow u : list) {
				row = sheet.createRow(rowIndex++);
				// 交易主体,
				cell = row.createCell(0);
				cell.setCellValue(u.getAccountBody());
				// 交易主体账号,
				cell = row.createCell(1);
				cell.setCellValue(u.getAccountBodyNumber());
				// 交易主体卡号,
				cell = row.createCell(2);
				cell.setCellValue(u.getAccountBodyCard());
				// 收付标志,
				cell = row.createCell(3);
				cell.setCellValue(u.getSign() == 0 ? "进" : "出");
				// 交易日期,
				cell = row.createCell(4);
				cell.setCellValue(u.getTradeDate() == null ? "" : simpleDateFormat.format(u.getTradeDate()));
				// 交易对手,
				cell = row.createCell(5);
				cell.setCellValue(u.getAccountRival());
				// 交易对手账号,
				cell = row.createCell(6);
				cell.setCellValue(u.getAccountRivalNumber());
				// 交易对手卡号,
				cell = row.createCell(7);
				cell.setCellValue(u.getAccountRivalCard());
				// 交易金额,
				cell = row.createCell(8);
				cell.setCellValue(u.getAccountMoney() == null ? "" : String.valueOf(u.getAccountMoney()));
				// 交易后余额,
				cell = row.createCell(9);
				cell.setCellValue(u.getSurplusMoney() == null ? "" : String.valueOf(u.getSurplusMoney()));
				// 摘要,
				cell = row.createCell(10);
				cell.setCellValue(u.getContentAbstract());
				// 交易主体归属行,
				cell = row.createCell(11);
				cell.setCellValue(u.getAccountBodyBank());
				// 交易对手归属行,
				cell = row.createCell(12);
				cell.setCellValue(u.getAccountRivalBank());
				// 交易地点,
				cell = row.createCell(13);
				cell.setCellValue(u.getAccountAddress());
				// 交易方式,
				cell = row.createCell(14);
				cell.setCellValue(u.getAccountMode());
				// 交易流水,
				cell = row.createCell(15);
				cell.setCellValue(u.getSerialNumber());
				// mac,
				cell = row.createCell(16);
				cell.setCellValue(u.getMacAddress());
				// ip,
				cell = row.createCell(17);
				cell.setCellValue(u.getIpAddress());
				// 币种,
				cell = row.createCell(18);
				cell.setCellValue(u.getCurrency());
				// 备注,
				cell = row.createCell(19);
				cell.setCellValue(u.getRemarks());
				// 交易机构号,
				cell = row.createCell(20);
				cell.setCellValue(u.getTradingOrganizationNumber());
				// 柜员号,
				cell = row.createCell(21);
				cell.setCellValue(u.getTellerNumber());
				// 对方机构号,
				cell = row.createCell(22);
				cell.setCellValue(u.getInstitutionNumber());

			}
			DownloadUtils.writeExcel2(wb, pathUUID, "MRD-" + byDealFlowUuid.get(i).getFileName() + "-" + i);
		}
		File zip = new File(path, "银行账单" + simpleDateFormat2.format(new Date()) + ".zip");
		File uuidDir = new File(pathUUID);
		if (uuidDir != null && uuidDir.isDirectory()) {
			File[] files = uuidDir.listFiles();
			List<File> xlsx = Arrays.stream(files).filter(m -> m.getName().contains("xlsx"))
					.collect(Collectors.toList());
			ZipUtils.ZipFiles(xlsx, zip);
		}
		DownloadUtils.downFileByStream2(response, zip, "银行账单" + simpleDateFormat2.format(new Date()), pathUUID);
		dataOperationLogService.save(null, super.getLoginUser(), new DealFlow(), new DealFlow(), SystemFlag.ZD,
				ModuleFlag.BILLEXPORT, ActionType.EXPORT);
	}

	@RequestMapping("/uploadFileDate")
	@ResponseBody
	public Result uploadFileDate() {
		String uid = request.getSession().getAttribute("caseId") == null ? ""
				: request.getSession().getAttribute("caseId").toString();
		try {
			dealFlowService.batchUpdate(uid);
		} catch (Exception e) {
		} finally {
			dealFlowService.clearCache1(uid);
			dealFlowService.clearCache2(uid);
			dealFlowService.clearCache3(uid);
			dealFlowService.clearCache4(uid);
			dealFlowService.clearCache5(uid);
		}
		return new Result(true, StatusCode.OK, "更新成功");
	}

	@RequestMapping("/findDealFlowListByPage") // /{page}/{size}
	@ResponseBody
	public Object findDealFlowListByPage(@RequestBody ExceptionAnalysisDto dto) { // Map<String,String> map,
																					// @PathVariable int page,
																					// @PathVariable int size
		String uuid = request.getSession().getAttribute("caseId") == null ? ""
				: request.getSession().getAttribute("caseId").toString();
		HashMap<String, String> map = new HashMap<String, String>();
		map.put("accountBodyCard", dto.getAccountBodyCard());
		map.put("accountRivalCard", dto.getAccountRivalCard());
		map.put("beginTime", dto.getBeginTime());
		map.put("endTime", dto.getEndTime());
		map.put("maxAccountMoney", dto.getMaxAccountMoney());
		map.put("maxTradeMoney", dto.getMaxTradeMoney());
		map.put("minAccountMoney", dto.getMinAccountMoney());
		map.put("minTradeMoney", dto.getMinTradeMoney());
		map.put("sign", dto.getSign());
		map.put("casemanageId", uuid);
		map.put("month", dto.getMonth());
		map.put("accountBody", dto.getAccountBody());
		map.put("accountRival", dto.getAccountRival());
		map.put("content_abstract", dto.getContent_abstract());
		PageResult pageResult = dealFlowService.getPageDetails(map, dto.getPageNumber(), dto.getPageSize());
		return pageResult;
	}

	@RequestMapping("/findDealFlowListByPage_easyui") // /{page}/{size}
	@ResponseBody
	public Object findDealFlowListByPage_easyui(ExceptionAnalysisDto dto) { // Map<String,String> map, @PathVariable int
																			// page, @PathVariable int size
		PageResult pageResult = dealFlowService.getPageDetails_easyui(dto);
		return pageResult;
	}
	@RequestMapping(method= RequestMethod.POST,value = "findDealFlowListExcel")
	@ResponseBody
	public void findDealFlowListExcel(@RequestBody ExceptionAnalysisDto dto, HttpServletResponse response )throws Exception{
		XSSFWorkbook workbook1 = new XSSFWorkbook();
		SXSSFWorkbook wb = new SXSSFWorkbook(workbook1, 100);
		//创建HSSFWorkbook对象(excel的文档对象)
		//建立新的sheet对象（excel的表单）
		Sheet sheet=wb.createSheet("交易详情");
		//在sheet里创建第二行
		Row row2=sheet.createRow(0);
		//创建单元格并设置单元格内容
		row2.createCell(0).setCellValue("交易主体");
		row2.createCell(1).setCellValue("交易主体账号");
		row2.createCell(2).setCellValue("交易主体卡号");
		row2.createCell(3).setCellValue("交易对手");
		row2.createCell(4).setCellValue("交易对手账号");
		row2.createCell(5).setCellValue("交易对手卡号");
		row2.createCell(6).setCellValue("交易时间");
		row2.createCell(7).setCellValue("交易类型");
		row2.createCell(8).setCellValue("交易金额");
		row2.createCell(9).setCellValue("交易后余额");
		row2.createCell(10).setCellValue("摘要");
		row2.createCell(11).setCellValue("对手归属行");
		row2.createCell(12).setCellValue("主体归属行");
		row2.createCell(13).setCellValue("对方机构号");
		row2.createCell(14).setCellValue("柜员号");
		row2.createCell(15).setCellValue("备注");
		//在sheet里创建第三行
		dto.setPage(1);dto.setRows(Integer.MAX_VALUE);
		PageResult countDetail = dealFlowService.getPageDetails_easyui(dto);
		List<DealFlow> content = countDetail.getRows();
		if (content!=null && content.size()>0){
			for (int i=0;i<content.size();i++){
				DealFlow map = content.get(i);
				Row row3=sheet.createRow(i+1);
				row3.createCell(0).setCellValue(map.getAccountBody());
				row3.createCell(1).setCellValue(map.getAccountBodyNumber());
				row3.createCell(2).setCellValue(map.getAccountBodyCard());
				row3.createCell(3).setCellValue(map.getAccountRival());
				row3.createCell(4).setCellValue(map.getAccountRivalNumber());
				row3.createCell(5).setCellValue(map.getAccountRivalCard());
				row3.createCell(6).setCellValue(DateUtils.getDateString((Date) map.getTradeDate()));
				row3.createCell(7).setCellValue(map.getSign()==0?"进":"出");
				row3.createCell(8).setCellValue(map.getAccountMoney()==null?"0":map.getAccountMoney().toString());
				row3.createCell(9).setCellValue(map.getSurplusMoney()==null?"0":map.getSurplusMoney().toString());
				row3.createCell(10).setCellValue((String) map.getContentAbstract());
				row3.createCell(11).setCellValue((String) map.getAccountRivalBank());
				row3.createCell(12).setCellValue((String) map.getAccountBodyBank());
				row3.createCell(13).setCellValue((String) map.getInstitutionNumber());
				row3.createCell(14).setCellValue((String) map.getTellerNumber());
				row3.createCell(15).setCellValue((String) map.getRemarks());
			}
		}
		DateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmssSSS");
		String fileName = "交易次数详情" + dateFormat.format(new Date());
		//输出Excel文件
		OutputStream output=response.getOutputStream();
		response.reset();
		response.setHeader("Content-disposition", "attachment; filename="+ ExcelUtils.toUtf8String(fileName+".xlsx"));
		response.setContentType("application/msexcel");
		wb.write(output);
		output.close();
		//添加导出日志
		dataOperationLogService.save(null, super.getLoginUser(), new PhoneRecord(), new PhoneRecord(), SystemFlag.ZD, ModuleFlag.BILLEXPORT, ActionType.EXPORT);

	}

	/**
	 * @Author 刘中华
	 * @Date 2019/3/28 10:53
	 * @Param 通用模板
	 * @return
	 * @Description
	 **/

	@RequestMapping("xx")
	@ResponseBody
	public void xx(@RequestParam("fileUpload") MultipartFile[] files, HttpServletRequest request,
			HttpServletResponse response,String uid) throws Exception {
		List<Map<String, Object>> mapList = new ArrayList<>();
		Map<String,Object> logMap = new LinkedHashMap();
		int a=0,b=0;
		String uuid = null;
		String filename = null;
		List<DataOperationImportLog> dils = new ArrayList<DataOperationImportLog>();
		try {
			for (int i = 0; i < files.length; i++) {
				Map<String, Object> map = new HashMap<>();
				try {
					MultipartFile file = files[i];
					filename = file.getOriginalFilename().split("\\.")[0];
					// uuid = UUID.randomUUID().toString().replace("-","");
					// 判断文件类型
					if (file == null || file.getSize() <= 0) {
						map.put("error", file.getOriginalFilename());
					}
					uuid = MD5.md5HashCode(file.getInputStream());
					DataOperationImportLog dil = new DataOperationImportLog();
					List<DataOperationImportLog> doi = dataOperationImportLogService
							.findByDealFlowUuidAndIsDeleteAndTableFlat(uuid, uid, 1);
					if (null == doi || doi.isEmpty()) {
						String result = ExcelUtils.getFileSort(file);
						HashMap<String, Object> paraMap = new HashMap<>();
						paraMap.put("uuid", uuid);
						paraMap.put("uid", uid);
						paraMap.put("platManagerId", super.getLoginUser().getId());
						if ("excel".equals(result)) {
							map = templateMatchService.xxx(file, paraMap);
						} else if ("csv".equals(result)) {
							map = dealFlowTitlePoolService.leadFileCSV(file, uid, super.getLoginUser().getId(), uuid);
						} else if ("txt".equals(result)) {
							map = templateMatchService.importTxtTemplete(file, uid, super.getLoginUser().getId(), uuid);
						} else if ("pdf".equals(result)) {
							map = dealFlowTitleService.pdfAnalysis(file, uid, super.getLoginUser(), uuid);
						}
						dil.setImportResult("成功");
					}else {
						map.put("errorInfo", filename + "为重复导入账单文件");
						dil.setImportResult("失败");
					}
					map.put("fileName", file.getOriginalFilename());
					mapList.add(map);
					dil.setFileName(file.getOriginalFilename());
					dil.setDealFlowUuid(uuid);
					dil.setSuccessNumber(map.get("successNum") == null ? 0 : Integer.parseInt(map.get("successNum").toString()));
					if (map.get("successNum")==null||Integer.parseInt(map.get("successNum").toString())==0){
						a++;//失败
					}else {
						b++;//成功
					}
					dil.setTableFlat(filename.equals("开户基本信息表") ? 0 : 1);
					dil.setFileSize(String.valueOf(file.getSize()));
					dil.setUserId(super.getLoginUser().getId());
					dil.setCreateTime(new Timestamp(System.currentTimeMillis()));
					dil.setImportDesc(map.get("errorInfo") == null ? "" : map.get("errorInfo").toString());
					dils.add(dil);
				} catch (OLE2NotOfficeXmlFileException e) {
					map.put("successNum", 0);
					mapList.add(map);
					e.printStackTrace();
				} catch (Exception e) {
					map.put("successNum", 0);
					mapList.add(map);
					e.printStackTrace();
				}
			}
			// 插入导入日志
			logMap = dataOperationLogService.save(files.length, dils, super.getLoginUser(), SystemFlag.ZD,
					ModuleFlag.BILLINPUT, ActionType.INPUT, uid);
			logMap.put("totalFile",dils.size());
			logMap.put("successFile",b);
			logMap.put("errorFile",a);
			response.getWriter().print(GsonUtil.GsonString(SUCCESS(logMap)));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				for (int i = 0; i < files.length; i++) {
					MultipartFile file = files[i];
					// 文件上传
					super.persistFile(file);
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
			dealFlowService.clearCache1(uid);
			dealFlowService.clearCache2(uid);
			dealFlowService.clearCache3(uid);
			dealFlowService.clearCache4(uid);
			dealFlowService.clearCache5(uid);

			ThreadPoolUtils.getInstance().getThreadPool().execute(new Runnable() {

				@Override
				public void run() {
					try {
						dealFlowService.batchUpdate(uid);
					} catch (Exception e2) {
					} finally {
						dealFlowService.clearCache1(uid);
						dealFlowService.clearCache2(uid);
						dealFlowService.clearCache3(uid);
						dealFlowService.clearCache4(uid);
						dealFlowService.clearCache5(uid);
						// 更新人员信息
						dealFlowService.updatePersonInformation(uid);
					}
				}
			});
		}
	}

	/**
	  * 自定义导入匹配
	 * 
	 * @param files
	 * @param request
	 * @param response
	 * @throws Exception
	 */
	@RequestMapping("billImportMapping")
	@ResponseBody
	public void billImportMapping(@RequestParam("fileUpload") MultipartFile file, HttpServletRequest request,
			HttpServletResponse response,String uid) throws Exception {
		List<Map<String, Object>> mapList = new ArrayList<>();
		String uuid = null;
		String filename = null;
		try {
			Map<String, Object> map = new HashMap<>();
			try {
				filename = file.getOriginalFilename().split("\\.")[0];
				uuid = MD5.md5HashCode(file.getInputStream());
				// 判断文件类型
				if (file == null || file.getSize() <= 0) {
					map.put("error", file.getOriginalFilename());
				}
				String result = ExcelUtils.getFileSort(file);
				HashMap<String, Object> paraMap = new HashMap<>();
				paraMap.put("uuid", uuid);
				paraMap.put("uid", uid);
				paraMap.put("excelName", file.getOriginalFilename());
				paraMap.put("platManagerId", super.getLoginUser().getId());
				List<DataOperationImportLog> doi = dataOperationImportLogService.findByDealFlowUuidAndIsDeleteAndTableFlat(uuid, uid, 1);
				if (null != doi && !doi.isEmpty()) {
					response.getWriter().print(GsonUtil.GsonString(ERROR(filename + "重复导入文件")));
					return;
				}
				if ("excel".equals(result)) {
					// 最简单的模板
					try {
						dealFlowService.deleteTempTable(uid);
						POIFactory.dealMapping(file.getInputStream(), paraMap);
					} catch (OLE2NotOfficeXmlFileException e) {
						e.printStackTrace();
						map.put("errorInfo", "(excel格式错误，请把excel打开另存为xlsx格式)");
					} catch (Exception e) {
						e.printStackTrace();
					}
				} else if ("csv".equals(result)) {
					map = dealFlowTitlePoolService.leadFileCSV(file, uid, super.getLoginUser().getId(), uuid);
				} else if ("txt".equals(result)) {
					map = templateMatchService.importTxtTempleteMapping(file, uid, super.getLoginUser().getId(), uuid);
				} else if ("pdf".equals(result)) {
					map = dealFlowTitleService.pdfAnalysisMapping(file, uid, super.getLoginUser(), uuid);
				}

				map.put("fileName", filename);
				map.put("fileUuid", uuid);
				mapList.add(map);
			} catch (OLE2NotOfficeXmlFileException e) {
				map.put("successNum", 0);
				mapList.add(map);
				e.printStackTrace();
			} catch (Exception e) {
				map.put("successNum", 0);
				mapList.add(map);
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		response.getWriter().print(GsonUtil.GsonString(SUCCESS(mapList)));
	}
	
	/**
	  * 查询所有标题头集合
	 * 
	 * @return
	 */
	@ResponseBody
	@RequestMapping(value = "/findDealFlowTitle")
	public List<String> findDealFlowTitle() {
		Sort sort = new Sort(Direction.ASC, "id");
		Iterable<DealFlowTitle> dtps = dealFlowTitleService.findAll(sort);
		List<String> titles = new ArrayList<String>();
		for (DealFlowTitle dt : dtps) {
			titles.add(dt.getMappingField());
		}
		return titles;
	}

	/**
	 * 查询自定义导入文件内容
	 * 
	 * @param page
	 * @param rows
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	@ResponseBody
	@RequestMapping(value = "/findDealFlowTemp4Page", method = RequestMethod.POST)
	public PageResult findDealFlowTemp4Page(Integer page, Integer rows,String caseId) {
		return dealFlowService.findTemp(caseId, page, rows);
	}

	/**
	 * 查询自定义表字段个数
	 * 
	 * @return
	 */
	@ResponseBody
	@RequestMapping(value = "/findTempColumnSize")
	public Object findTempColumnSize(String caseId) {
		return dealFlowService.findTempCloumSize(caseId);
	}

	/**
	 * 匹配标题头
	 * 
	 * @return
	 */
	@ResponseBody
	@RequestMapping(value = "/TempTitleMapping")
	public Object TempTitleMapping(@RequestParam Map<String, String> paramTempMap) {
		return dealFlowTitleService.TempTitleMapping(paramTempMap);
	}
	
	/**
	 * 自定义导入
	 * 
	 * @return
	 */
	@ResponseBody
	@RequestMapping(value = "/zdyImport",method = RequestMethod.POST)
	public Object zdyImport(@RequestBody BillTempImportParams bp) {
		Map<String,Object> logMap = new LinkedHashMap<String,Object>();
		String caseId = bp.getCaseId();
		try {
			List<Map<String,Object>> tempResult = dealFlowTitleService.getMappingResultBySelectRowIndex(bp.getSelectRowIndex(), caseId);
			if(null != tempResult && tempResult.size() > 0 ) {
				List<String> insertSql = new ArrayList<String>();
				String whichTable = caseManageService.getWhichTable(caseId);
				Integer loginUserId = super.getLoginUser().getId();
				for(Map<String,Object> m : tempResult) {
					setDealFlowInsertSql(bp, caseId, insertSql, whichTable, loginUserId, m);
				}
				Integer successNum = dealFlowTitleService.excuteDealFlowInsert(insertSql);
				List<DataOperationImportLog> dils = new ArrayList<DataOperationImportLog>();
				DataOperationImportLog dil = new DataOperationImportLog();
				dil.setFileName(bp.getFileName());
				dil.setDealFlowUuid(bp.getFileUuid());
				dil.setSuccessNumber(successNum);
				dil.setTableFlat(1);
				dil.setFileSize(bp.getFileSize());
				dil.setUserId(loginUserId);
				dil.setCreateTime(new Timestamp(System.currentTimeMillis()));
				dil.setImportResult("成功");
				dils.add(dil);
				// 插入导入日志
				logMap = dataOperationLogService.save(1, dils, super.getLoginUser(), SystemFlag.ZD, ModuleFlag.BILLINPUT, ActionType.INPUT, caseId);
			}
		} catch (Exception e) {
			logMap.put("errorMsg", e.getCause().getMessage());
			e.printStackTrace();
		} finally {
			dealFlowService.clearCache1(caseId);
			dealFlowService.clearCache2(caseId);
			dealFlowService.clearCache3(caseId);
			dealFlowService.clearCache4(caseId);
			dealFlowService.clearCache5(caseId);
			ThreadPoolUtils.getInstance().getThreadPool().execute(new Runnable() {
				@Override
				public void run() {
					try {
						dealFlowService.batchUpdate(caseId);
					} catch (Exception e2) {
					} finally {
						dealFlowService.clearCache1(caseId);
						dealFlowService.clearCache2(caseId);
						dealFlowService.clearCache3(caseId);
						dealFlowService.clearCache4(caseId);
						dealFlowService.clearCache5(caseId);
						// 更新人员信息
						dealFlowService.updatePersonInformation(caseId);
					}
				}
			});
		}
		return logMap;
	}

	
	
	/**
	  * 拼接insert sql
	 * @param bp
	 * @param caseId
	 * @param insertSql
	 * @param whichTable
	 * @param loginUserId
	 * @param m
	 */
	private void setDealFlowInsertSql(BillTempImportParams bp, String caseId, List<String> insertSql, String whichTable,
			Integer loginUserId, Map<String, Object> m) {
		StringBuffer sbf = new StringBuffer("INSERT INTO "+whichTable+" (BILL_ID,UUID,CREATE_TIME,CASEMANAGE_ID,CREATE_ID,");
		for (Map.Entry<String, String> entry : bp.getMappingTitle().entrySet()) {
			String mappingField = dealFlowTitleService.getMappingFieldByMv(entry.getValue());
			sbf.append(mappingField+",");//拼接insert 标题
		}
		sbf.deleteCharAt(sbf.length() - 1);
		sbf.append(") values(SEQ_DEAL_FLOW.nextval,'"+bp.getFileUuid()+"',sysdate,'"+caseId+"',"+loginUserId+",");
		for (Map.Entry<String, String> entry : bp.getMappingTitle().entrySet()) {
			if(entry.getValue().equals("日期时间")) {
				if(null != m.get(entry.getKey())) {
					String strTradDate = m.get(entry.getKey()).toString();
					if(null != strTradDate) {
						sbf.append("to_date('"+strTradDate+"','yyyy-mm-dd hh24:mi:ss'),");//拼接insert 值
					}else {
						sbf.append("null,");//拼接insert 值
					}
				}else {
					sbf.append("null,");//拼接insert 值
				}
			}else if(entry.getValue().equals("收付标志")) {
				if(null != m.get(entry.getKey())) {
					String sign = m.get(entry.getKey()).toString();
					if ("0".equals(sign)||"C".equals(sign)||"进".equals(sign)||"贷".equals(sign)){
						sbf.append("0,");
					} else if ("1".equals(sign)||"D".equals(sign)||"出".equals(sign)||"借".equals(sign)){
						sbf.append("1,");
					}
				}else {
					sbf.append("null,");//拼接insert 值
				}
			}else if(entry.getValue().equals("交易金额")){
				if(null != m.get(entry.getKey())) {
					String tranMoney = m.get(entry.getKey()).toString();
					if (!"".equals(tranMoney)&&tranMoney!=null&&new BigDecimal(tranMoney).compareTo(new BigDecimal(0))!=0){
						sbf.append(new BigDecimal(tranMoney)+",");
					}else {
						sbf.append("0,");
					}
				}else {
					sbf.append("0,");
				}
			}else if(entry.getValue().equals("交易后余额")){
				if(null != m.get(entry.getKey())) {
					String surplusMoney = m.get(entry.getKey()).toString();
					if (!"".equals(surplusMoney)&&surplusMoney!=null&&new BigDecimal(surplusMoney).compareTo(new BigDecimal(0))!=0){
						sbf.append(new BigDecimal(surplusMoney)+",");
					}else {
						sbf.append("0,");
					}
				}else {
					sbf.append("0,");
				}
			}else if(entry.getValue().equals("交易主体卡号")){
				if(null == m.get(entry.getKey())) {
					String accountBodyKey = "";
					for (Map.Entry<String, String> entryA : bp.getMappingTitle().entrySet()) {
						if(entryA.getValue().equals("交易主体账号")) {
							accountBodyKey = entryA.getKey();
							break;
						}
					}
					sbf.append("'"+m.get(accountBodyKey)+"',");//拼接insert 值
				}else {
					sbf.append("'"+m.get(entry.getKey())+"',");//拼接insert 值
				}
			}else if(entry.getValue().equals("交易对手卡号")){
				if(null == m.get(entry.getKey())) {
					String accountBodyRivalCardKey = "";
					for (Map.Entry<String, String> entryA : bp.getMappingTitle().entrySet()) {
						if(entryA.getValue().equals("交易对手账号")) {
							accountBodyRivalCardKey = entryA.getKey();
							break;
						}
					}
					sbf.append("'"+m.get(accountBodyRivalCardKey)+"',");//拼接insert 值
				}else {
					sbf.append("'"+m.get(entry.getKey())+"',");//拼接insert 值
				}
			}else {
				if(null != m.get(entry.getKey())) {
					sbf.append("'"+m.get(entry.getKey())+"',");//拼接insert 值
				}else {
					sbf.append("null,");//拼接insert 值
				}
			}
		}
		sbf.deleteCharAt(sbf.length() - 1);
		sbf.append(") ");
		insertSql.add(sbf.toString());
	}
}
